package net.adst.util;

import com.toedter.calendar.JDateChooser;
import net.adst.beans.NaemTec;
import net.adst.beans.OurTecRow;
import net.adst.resources.Constants;
import net.adst.resources.VariableNames;
import net.adst.util.fileFilters.FileOutputStreamFactory;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import sun.net.ResourceManager;

import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

/**
 * Created with IntelliJ IDEA.
 * User: alekseipot
 * Date: 19.11.12
 * Time: 4:32
 * To change this template use File | Settings | File Templates.
 */
public class FileHelper {
    public static void createResultExcelFile(File file) {
        try {
            if (file.exists()) {
                file.delete();
            }
            FileOutputStream out = FileOutputStreamFactory.getFileOutputStreamFromFactory(file);
            if (out == null) {
                return;
            }

            DefaultTableModel model = (DefaultTableModel) DataManager.getObject(VariableNames.UI_NAEM_DETAILED_MODEL);
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("Наем. тех.");
            HSSFCellStyle style = wb.createCellStyle();
            style.setFillBackgroundColor(HSSFColor.GREEN.index);
            style.setFillForegroundColor(HSSFColor.RED.index);
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setBorderTop(CellStyle.BORDER_THIN);

            HSSFCellStyle cellStyle = wb.createCellStyle();
            cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyle.setBorderRight(CellStyle.BORDER_THIN);
            cellStyle.setBorderTop(CellStyle.BORDER_THIN);


            CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 2, 0, 19);
            sheet.setAutoFilter(cellRangeAddress);
            HSSFRow topNaemRow = sheet.createRow(0);
            HSSFCell topNAemCell = topNaemRow.createCell(4);
            Calendar dtFrom = (Calendar) ((JDateChooser) DataManager.getObject(VariableNames.CALENDAR_DATE_FROM)).getCalendar().clone();
            Calendar dtTo = (Calendar) ((JDateChooser) DataManager.getObject(VariableNames.CALENDAR_DATE_TO)).getCalendar().clone();
            topNAemCell.setCellValue("Данные о наемной техники за период с " + (new SimpleDateFormat("dd.MM.yyyy")).format(dtFrom.getTime()) + " по " + (new SimpleDateFormat("dd.MM.yyyy")).format(dtTo.getTime()));
            topNaemRow.setHeight(new Short("500"));

            HSSFRow row = null;
            row = sheet.createRow(2);

            for (int i = 0; i < Constants.NAEM_НEADER.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(Constants.NAEM_НEADER[i]);
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, (Constants.NAEM_НEADER[i].toCharArray().length * 5 + 5) * 100);
            }


            for (int j = 0; j < model.getRowCount(); j++) {
                row = sheet.createRow(3 + j);
                for (int i = 0; i < Constants.NAEM_НEADER.length; i++) {
                    if (i != Constants.NAEM_НEADER.length - 1) {
                        HSSFCell cell = row.createCell(i);
                        if (i == 6 || i == 9 || i == 10 || i == 12 || i == 13 || i == 14 || i == 15 || i == 17 || i == 18) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(!(String.valueOf(model.getValueAt(j, i))).isEmpty() ? Double.valueOf(model.getValueAt(j, i).toString()) : new Double(0));
                        } else {
                            cell.setCellValue((String) model.getValueAt(j, i));
                        }
                        cell.setCellStyle(cellStyle);
                    } else {
                        ArrayList<NaemTec> rows = (ArrayList<NaemTec>) DataManager.getObject(VariableNames.TEC_NAEM_USING_ROWS);
                        String[] comment = rows.get(j).getComment().split("\n");
                        for (int k = 0; k < comment.length; k++) {
                            HSSFCell cell = row.createCell(i + k);
                            cell.setCellValue(comment[k]);
                            cell.setCellStyle(cellStyle);
                        }
                    }
                }
            }

            HSSFRow sumRowNaem = sheet.createRow(sheet.getLastRowNum() + 1);
            HSSFCell sumCell0 = sumRowNaem.createCell(0);
            sumCell0.setCellValue("Итого");

            HSSFCell sumCell1 = sumRowNaem.createCell(9);
            sumCell1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell1.setCellFormula("SUM(J4:J" + String.valueOf(sheet.getLastRowNum()) + ")");

            HSSFCell sumCell2 = sumRowNaem.createCell(10);
            sumCell2.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell2.setCellFormula("SUM(K4:K" + String.valueOf(sheet.getLastRowNum()) + ")");

            HSSFCell sumCell3 = sumRowNaem.createCell(13);
            sumCell3.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell3.setCellFormula("SUM(N4:N" + String.valueOf(sheet.getLastRowNum()) + ")");


            HSSFCell sumCell4 = sumRowNaem.createCell(14);
            sumCell4.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell4.setCellFormula("SUM(O4:O" + String.valueOf(sheet.getLastRowNum()) + ")");

            HSSFCell sumCell5 = sumRowNaem.createCell(15);
            sumCell5.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell5.setCellFormula("SUM(P4:P" + String.valueOf(sheet.getLastRowNum()) + ")");

            HSSFCell sumCell6 = sumRowNaem.createCell(17);
            sumCell6.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell6.setCellFormula("SUM(R4:R" + String.valueOf(sheet.getLastRowNum()) + ")");

            HSSFCell sumCell7 = sumRowNaem.createCell(18);
            sumCell7.setCellType(HSSFCell.CELL_TYPE_FORMULA);
            sumCell7.setCellFormula("SUM(S4:S" + String.valueOf(sheet.getLastRowNum()) + ")");

            for (int i = 0; i < Constants.NAEM_НEADER.length; i++) {
                if (sumRowNaem.getCell(i) != null) {
                    sumRowNaem.getCell(i).setCellStyle(cellStyle);
                } else {
                    sumRowNaem.createCell(i).setCellStyle(cellStyle);
                }
            }

            DefaultTableModel modelOur = (DefaultTableModel) DataManager.getObject(VariableNames.UI_OUR_DETAILED_MODEL);
            HSSFSheet sheetOur;
            ArrayList<OurTecRow> rows = (ArrayList<OurTecRow>) DataManager.getObject(VariableNames.TEC_OUR_USING_ROWS);
            for (int j = 0; j < modelOur.getRowCount(); j++) {
                if (wb.getSheet(modelOur.getValueAt(j, 1).toString()) != null) {
                    sheetOur = wb.getSheet(modelOur.getValueAt(j, 1).toString());
                } else {

                    sheetOur = wb.createSheet(modelOur.getValueAt(j, 1).toString());
                    HSSFRow topOurRow = sheetOur.createRow(0);
                    HSSFCell topOurCell = topOurRow.createCell(4);
                    topOurCell.setCellValue("Данные за период с " + (new SimpleDateFormat("dd.MM.yyyy")).format(dtFrom.getTime()) + " по " + (new SimpleDateFormat("dd.MM.yyyy")).format(dtTo.getTime()));
                    topOurRow.setHeight(new Short("450"));

                    HSSFRow tecNameRow = sheetOur.createRow(1);
                    HSSFCell tecNameCell = tecNameRow.createCell(4);
                    tecNameCell.setCellValue(rows.get(j).getShortName());
                    tecNameRow.setHeight(new Short("450"));

                    HSSFRow rowOurF = sheetOur.createRow(3);
                    sheetOur.setAutoFilter(new CellRangeAddress(3, 3, 0, 20));
                    for (int i = 0; i < Constants.OUR_НEADER.length; i++) {
                        HSSFCell cell = rowOurF.createCell(i);
                        cell.setCellValue(Constants.OUR_НEADER[i]);
                        cell.setCellStyle(style);
                        sheetOur.setColumnWidth(i, (Constants.OUR_НEADER[i].toCharArray().length * 5 + 5) * 100);
                    }
                }

                HSSFRow rowOur = sheetOur.createRow(1 + sheetOur.getLastRowNum());
                for (int i = 0; i < Constants.OUR_НEADER.length; i++) {
                    if (i != Constants.OUR_НEADER.length - 1) {
                        HSSFCell cell = rowOur.createCell(i);
                        if (i == 5 || i == 6 || i == 7 || i == 8 || i == 12 || i == 13 || i == 14 || i == 15 || i == 17 || i == 18 || i == 19) {
                            cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(!(String.valueOf(modelOur.getValueAt(j, i))).isEmpty() ? Double.valueOf(modelOur.getValueAt(j, i).toString()) : new Double(0));
                        } else {
                            cell.setCellValue((String) modelOur.getValueAt(j, i));
                        }
                        cell.setCellStyle(cellStyle);
                    } else {
                        String[] comment = rows.get(j).getComment().split("\n");
                        for (int k = 0; k < comment.length; k++) {
                            HSSFCell cell = row.createCell(i + k);
                            cell.setCellValue(comment[k]);
                            cell.setCellStyle(cellStyle);
                        }
                    }
                }
            }

            for (int i = 1; i < wb.getNumberOfSheets(); i++) {
                HSSFSheet sh = wb.getSheetAt(i);
                HSSFRow sumRowOur = sh.createRow(sh.getLastRowNum() + 1);
                HSSFCell smCell0 = sumRowOur.createCell(0);
                smCell0.setCellValue("Итого");

                HSSFCell smCell1 = sumRowOur.createCell(6);
                smCell1.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell1.setCellFormula("SUM(G5:G" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell2 = sumRowOur.createCell(8);
                smCell2.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell2.setCellFormula("SUM(I5:I" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell3 = sumRowOur.createCell(12);
                smCell3.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell3.setCellFormula("SUM(M5:M" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell4 = sumRowOur.createCell(13);
                smCell4.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell4.setCellFormula("SUM(N5:N" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell5 = sumRowOur.createCell(14);
                smCell5.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell5.setCellFormula("SUM(O5:O" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell6 = sumRowOur.createCell(15);
                smCell6.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell6.setCellFormula("SUM(P5:P" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell9 = sumRowOur.createCell(17);
                smCell9.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell9.setCellFormula("SUM(R5:R" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell10 = sumRowOur.createCell(18);
                smCell10.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell10.setCellFormula("SUM(S5:S" + String.valueOf(sh.getLastRowNum()) + ")");

                HSSFCell smCell11 = sumRowOur.createCell(19);
                smCell11.setCellType(HSSFCell.CELL_TYPE_FORMULA);
                smCell11.setCellFormula("SUM(T5:T" + String.valueOf(sh.getLastRowNum()) + ")");

                for (int j = 0; j < Constants.OUR_НEADER.length; j++) {
                    if (sumRowOur.getCell(j) != null) {
                        sumRowOur.getCell(j).setCellStyle(cellStyle);
                    } else {
                        sumRowOur.createCell(j).setCellStyle(cellStyle);
                    }
                }

            }

            wb.write(out);
            out.close();
            JOptionPane.showMessageDialog(null, "Файл успешно сохранен.", "Файл сохранен", JOptionPane.OK_OPTION, new ImageIcon(ResourceManager.class.getResource("/images/success.png")));
        } catch (FileNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (IOException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

    }


    public static boolean verifyFileName(File directory, File file) {
        String fileName = file.getName();
        if (!file.getName().contains(".xls")) {
            fileName += ".xls";
        }
        File[] files = null;
        if (directory.isDirectory()) {
            files = directory.listFiles(new FileFilter() {
                @Override
                public boolean accept(File pathname) {
                    int position = pathname.getName().lastIndexOf(".");
                    String filename = pathname.getName().substring(position + 1);
                    return filename.contains("xls");
                }
            });
        }
        for (File f : files) {
            if (f.getName().equals(fileName)) {
                return false;
            }
        }
        return true;
    }


}
